#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CY.GFive.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace CY.GFive.DALProviders.SqlServerProvider
{
	public class DeviceInfoProvider : CY.GFive.Core.Providers.DALProvider.IDeviceInfoProvider
	{
		#region Sql
		private static readonly string SqlSelectDeviceInfo = "SELECT [Id], [DeviceName],[DeviceCode],[ProDate],[ProFac],[FacAddress],[FacPhone],[Zip_Code],[Contactor],[BuyDate],[CheckDate],[StartDate],[DeviceLvl],[DevDes],[AidDevDes],[DevNum],[ClassRoomCode],[DepartCode] FROM [DeviceInfo] WHERE  [Id]=@Id";

		private static readonly string SqlInsertDeviceInfo = "INSERT INTO [DeviceInfo]([DeviceName],[DeviceCode],[ProDate],[ProFac],[FacAddress],[FacPhone],[Zip_Code],[Contactor],[BuyDate],[CheckDate],[StartDate],[DeviceLvl],[DevDes],[AidDevDes],[DevNum],[ClassRoomCode],[DepartCode]) values(@DeviceName,@DeviceCode,@ProDate,@ProFac,@FacAddress,@FacPhone,@Zip_Code,@Contactor,@BuyDate,@CheckDate,@StartDate,@DeviceLvl,@DevDes,@AidDevDes,@DevNum,@ClassRoomCode,@DepartCode);SELECT SCOPE_IDENTITY();";

		private static readonly string SqlUpdateDeviceInfo = "UPDATE [DeviceInfo] SET [DeviceName] = @DeviceName,[DeviceCode] = @DeviceCode,[ProDate] = @ProDate,[ProFac] = @ProFac,[FacAddress] = @FacAddress,[FacPhone] = @FacPhone,[Zip_Code] = @Zip_Code,[Contactor] = @Contactor,[BuyDate] = @BuyDate,[CheckDate] = @CheckDate,[StartDate] = @StartDate,[DeviceLvl] = @DeviceLvl,[DevDes] = @DevDes,[AidDevDes] = @AidDevDes,[DevNum] = @DevNum,[ClassRoomCode] = @ClassRoomCode,[DepartCode] = @DepartCode WHERE  [Id]=@Id";

		private static readonly string SqlDeleteDeviceInfo = "DELETE [DeviceInfo] WHERE  [Id]=@Id";

		private static readonly string SqlGetAllDeviceInfo = "SELECT [Id], [DeviceName],[DeviceCode],[ProDate],[ProFac],[FacAddress],[FacPhone],[Zip_Code],[Contactor],[BuyDate],[CheckDate],[StartDate],[DeviceLvl],[DevDes],[AidDevDes],[DevNum],[ClassRoomCode],[DepartCode] FROM [DeviceInfo]";
		#endregion

		#region IDeviceInfoProvider Members
		public Core.Business.DeviceInfo Select(int id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, id);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectDeviceInfo);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.DeviceInfo deviceInfo = new Core.Business.DeviceInfo();

				if (!reader.IsDBNull(0)) deviceInfo.Id = reader.GetInt32(0);
				if (!reader.IsDBNull(1)) deviceInfo.DeviceName = reader.GetString(1);
				if (!reader.IsDBNull(2)) deviceInfo.DeviceCode = reader.GetString(2);
				if (!reader.IsDBNull(3)) deviceInfo.ProDate = reader.GetDateTime(3);
				if (!reader.IsDBNull(4)) deviceInfo.ProFac = reader.GetString(4);
				if (!reader.IsDBNull(5)) deviceInfo.FacAddress = reader.GetString(5);
				if (!reader.IsDBNull(6)) deviceInfo.FacPhone = reader.GetString(6);
				if (!reader.IsDBNull(7)) deviceInfo.Zip_Code = reader.GetString(7);
				if (!reader.IsDBNull(8)) deviceInfo.Contactor = reader.GetString(8);
				if (!reader.IsDBNull(9)) deviceInfo.BuyDate = reader.GetDateTime(9);
				if (!reader.IsDBNull(10)) deviceInfo.CheckDate = reader.GetDateTime(10);
				if (!reader.IsDBNull(11)) deviceInfo.StartDate = reader.GetDateTime(11);
				if (!reader.IsDBNull(12)) deviceInfo.DeviceLvl = reader.GetInt32(12);
				if (!reader.IsDBNull(13)) deviceInfo.DevDes = reader.GetString(13);
				if (!reader.IsDBNull(14)) deviceInfo.AidDevDes = reader.GetString(14);
				if (!reader.IsDBNull(15)) deviceInfo.DevNum = reader.GetInt32(15);
				if (!reader.IsDBNull(16)) deviceInfo.ClassRoomCode = reader.GetString(16);
				if (!reader.IsDBNull(17)) deviceInfo.DepartCode = reader.GetString(17);

				return deviceInfo;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.DeviceInfo deviceInfo)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@DeviceName", SqlDbType.NVarChar, deviceInfo.DeviceName);
			sql.AddParameter("@DeviceCode", SqlDbType.NVarChar, deviceInfo.DeviceCode);
			sql.AddParameter("@ProDate", SqlDbType.DateTime, deviceInfo.ProDate);
			sql.AddParameter("@ProFac", SqlDbType.NVarChar, deviceInfo.ProFac);
			sql.AddParameter("@FacAddress", SqlDbType.NVarChar, deviceInfo.FacAddress);
			sql.AddParameter("@FacPhone", SqlDbType.NVarChar, deviceInfo.FacPhone);
			sql.AddParameter("@Zip_Code", SqlDbType.NVarChar, deviceInfo.Zip_Code);
			sql.AddParameter("@Contactor", SqlDbType.NVarChar, deviceInfo.Contactor);
			sql.AddParameter("@BuyDate", SqlDbType.DateTime, deviceInfo.BuyDate);
			sql.AddParameter("@CheckDate", SqlDbType.DateTime, deviceInfo.CheckDate);
			sql.AddParameter("@StartDate", SqlDbType.DateTime, deviceInfo.StartDate);
			sql.AddParameter("@DeviceLvl", SqlDbType.Int, deviceInfo.DeviceLvl);
			sql.AddParameter("@DevDes", SqlDbType.NVarChar, deviceInfo.DevDes);
			sql.AddParameter("@AidDevDes", SqlDbType.NVarChar, deviceInfo.AidDevDes);
			sql.AddParameter("@DevNum", SqlDbType.Int, deviceInfo.DevNum);
			sql.AddParameter("@ClassRoomCode", SqlDbType.NVarChar, deviceInfo.ClassRoomCode);
			sql.AddParameter("@DepartCode", SqlDbType.NVarChar, deviceInfo.DepartCode);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertDeviceInfo);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				if (!reader.IsDBNull(0)) deviceInfo.Id = int.Parse(reader.GetDecimal(0).ToString());

				reader.Close();
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void Update(Core.Business.DeviceInfo deviceInfo)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@DeviceName", SqlDbType.NVarChar, deviceInfo.DeviceName);
			sql.AddParameter("@DeviceCode", SqlDbType.NVarChar, deviceInfo.DeviceCode);
			sql.AddParameter("@ProDate", SqlDbType.DateTime, deviceInfo.ProDate);
			sql.AddParameter("@ProFac", SqlDbType.NVarChar, deviceInfo.ProFac);
			sql.AddParameter("@FacAddress", SqlDbType.NVarChar, deviceInfo.FacAddress);
			sql.AddParameter("@FacPhone", SqlDbType.NVarChar, deviceInfo.FacPhone);
			sql.AddParameter("@Zip_Code", SqlDbType.NVarChar, deviceInfo.Zip_Code);
			sql.AddParameter("@Contactor", SqlDbType.NVarChar, deviceInfo.Contactor);
			sql.AddParameter("@BuyDate", SqlDbType.DateTime, deviceInfo.BuyDate);
			sql.AddParameter("@CheckDate", SqlDbType.DateTime, deviceInfo.CheckDate);
			sql.AddParameter("@StartDate", SqlDbType.DateTime, deviceInfo.StartDate);
			sql.AddParameter("@DeviceLvl", SqlDbType.Int, deviceInfo.DeviceLvl);
			sql.AddParameter("@DevDes", SqlDbType.NVarChar, deviceInfo.DevDes);
			sql.AddParameter("@AidDevDes", SqlDbType.NVarChar, deviceInfo.AidDevDes);
			sql.AddParameter("@DevNum", SqlDbType.Int, deviceInfo.DevNum);
			sql.AddParameter("@ClassRoomCode", SqlDbType.NVarChar, deviceInfo.ClassRoomCode);
			sql.AddParameter("@DepartCode", SqlDbType.NVarChar, deviceInfo.DepartCode);
			sql.AddParameter("@Id", SqlDbType.Int, deviceInfo.Id);
			sql.ExecuteSql(SqlUpdateDeviceInfo);
		}

		public void Delete(Core.Business.DeviceInfo deviceInfo)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, deviceInfo.Id);
			sql.ExecuteSql(SqlDeleteDeviceInfo);
		}

		public IList<Core.Business.DeviceInfo> GetAllDeviceInfo()
		{
			IList<Core.Business.DeviceInfo> deviceInfolist = new List<Core.Business.DeviceInfo>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllDeviceInfo);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.DeviceInfo deviceInfo = new Core.Business.DeviceInfo();

					if (!reader.IsDBNull(0)) deviceInfo.Id = reader.GetInt32(0);
					if (!reader.IsDBNull(1)) deviceInfo.DeviceName = reader.GetString(1);
					if (!reader.IsDBNull(2)) deviceInfo.DeviceCode = reader.GetString(2);
					if (!reader.IsDBNull(3)) deviceInfo.ProDate = reader.GetDateTime(3);
					if (!reader.IsDBNull(4)) deviceInfo.ProFac = reader.GetString(4);
					if (!reader.IsDBNull(5)) deviceInfo.FacAddress = reader.GetString(5);
					if (!reader.IsDBNull(6)) deviceInfo.FacPhone = reader.GetString(6);
					if (!reader.IsDBNull(7)) deviceInfo.Zip_Code = reader.GetString(7);
					if (!reader.IsDBNull(8)) deviceInfo.Contactor = reader.GetString(8);
					if (!reader.IsDBNull(9)) deviceInfo.BuyDate = reader.GetDateTime(9);
					if (!reader.IsDBNull(10)) deviceInfo.CheckDate = reader.GetDateTime(10);
					if (!reader.IsDBNull(11)) deviceInfo.StartDate = reader.GetDateTime(11);
					if (!reader.IsDBNull(12)) deviceInfo.DeviceLvl = reader.GetInt32(12);
					if (!reader.IsDBNull(13)) deviceInfo.DevDes = reader.GetString(13);
					if (!reader.IsDBNull(14)) deviceInfo.AidDevDes = reader.GetString(14);
					if (!reader.IsDBNull(15)) deviceInfo.DevNum = reader.GetInt32(15);
					if (!reader.IsDBNull(16)) deviceInfo.ClassRoomCode = reader.GetString(16);
					if (!reader.IsDBNull(17)) deviceInfo.DepartCode = reader.GetString(17);

					deviceInfo.MarkOld();
					deviceInfolist.Add(deviceInfo);
				}
				reader.Close();
			}
			return deviceInfolist;
		}
		#endregion
	}
}
